import datetime

import numpy as np
import pandas as pd


def calculate_age_from_id(id_number):
    try:
        birth_date_str = id_number[6:14]  # 从身份证号中获取出生日期
        format_str = "%Y%m%d"
        birth_date = datetime.datetime.strptime(birth_date_str, format_str).date()
        today = datetime.date.today()
        age = today.year - birth_date.year - ((today.month, today.day) < (birth_date.month, birth_date.day))
        return age
    except ValueError:  # 处理由于不符合标准身份证号格式引发的错误
        print(f"{id_number}异常")
        return None


def whcd(df, search_column1, update_column1):
    """
    在Excel文件中特定列中填入数据，如果该行中的某一列包含指定关键字。

    :param file_path: Excel文件的路径
    :param search_column1: 要搜索的列的表头名称
    :param search_column2: 要搜索的列的表头名称
    :param search_column3: 要搜索的列的表头名称
    :param search_column4: 要搜索的列的表头名称
    :param search_column5: 要搜索的列的表头名称
    :param update_column: 要更新的列的表头名称
    """
    try:
        # 读取Excel文件
        df.replace(np.nan, "", inplace=True)  # 将NaN替换为None
        # 遍历每一行
        for index, row in df.iterrows():
            id_number = row[search_column1]  # 获取身份证号
            age = calculate_age_from_id(id_number)
            wh = str(row[update_column1])
            if age is None:  # 如果年龄为None，表明身份证号错误，跳过这一行
                continue
            if wh == '' or pd.isna(wh) or wh == "不详":
                if age <= 6:
                    df.at[index, update_column1] = "文盲或半文盲"
                elif 7 <= age <= 13:
                    df.at[index, update_column1] = "小学"
                elif 14 <= age <= 16:
                    df.at[index, update_column1] = "初中"
                elif 17 <= age <= 19:
                    df.at[index, update_column1] = "高中"
                elif 20 <= age <= 59:
                    df.at[index, update_column1] = "高中"
                elif 60 <= age <= 69:
                    df.at[index, update_column1] = "初中"
                elif 70 <= age:
                    df.at[index, update_column1] = "小学"
        # 保存更新后的Excel文件
        df[update_column1] = df[update_column1].astype(str)
        print("[文化程度]已完成处理。")
        return df
    except Exception as e:
        print(f"文化程度处理时发生错误：{e}")
